package com.handy.plugin.auth.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.handy.database.DBUtil;
import com.handy.plugin.auth.bean.HAuthRole;

/**
 * RoleDAO,角色数据表操作.
 * 
 * @author rocken.zeng@gmail.com
 * 
 */
public class HAuthRoleDAO {
	
	private DBUtil dbUtil = DBUtil.getInstance();

	/**
	 * 添加操作.
	 * 
	 * @param role
	 */
	public void save(HAuthRole role) {
		StringBuffer sb = new StringBuffer();
		sb.append("insert into ").append(TABLENAME).append(" ( ");
		sb.append(ID).append(",");
		sb.append(NAME).append(",");
		sb.append(NOTE).append(",");
		sb.append(USERID);
		sb.append(") values ( ");
		sb.append(TABLESEQ).append(".nextval,");//oracle
		sb.append("'").append(role.getName()).append("',");
		sb.append("'").append(role.getNote()).append("',");
		sb.append(role.getUserid());
		sb.append(")");

		if (log.isDebugEnabled()) {
			log.info("saving " + TABLENAME + " instance");
			log.info(sb.toString());
		}

		QueryRunner run = new QueryRunner(dbUtil.dataSource(getDataSourceName()));
		try {
			run.update(sb.toString());
		} catch (SQLException e) {
			log.error(e.getMessage());
		}
	}

	/**
	 * 删除操作.
	 * 
	 * @param roleId
	 */
	public void delete(Integer roleId) {
		StringBuffer sb = new StringBuffer();
		sb.append("delete from ").append(TABLENAME);
		sb.append(" where ").append(ID).append(" = ");
		sb.append(roleId);
		if (log.isDebugEnabled()) {
			log.info("deleting " + TABLENAME + " instance");
			log.info(sb.toString());
		}
		QueryRunner run = new QueryRunner(dbUtil.dataSource(getDataSourceName()));
		try {
			run.update(sb.toString());
		} catch (SQLException e) {
			log.error(e.getMessage());
		}
	}

	/**
	 * 修改操作.
	 * 
	 * @param role
	 */
	public void update(HAuthRole role) {
		StringBuffer sb = new StringBuffer();
		sb.append("update ").append(TABLENAME).append(" set ");
		sb.append(NAME).append(" = ").append("'")
				.append(role.getName()).append("',");
		sb.append(NOTE).append(" = ").append("'")
				.append(role.getNote()).append("'");
		sb.append(" where ").append(ID).append(" = ");
		sb.append(role.getId());

		if (log.isDebugEnabled()) {
			log.debug("update " + TABLENAME + " instance");
			log.debug(sb.toString());
		}

		QueryRunner run = new QueryRunner(dbUtil.dataSource(getDataSourceName()));
		try {
			run.update(sb.toString());
		} catch (SQLException e) {
			log.error(e.getMessage());
		}
	}

	/**
	 * 查找指定id的角色信息.
	 * 
	 * @param roleId
	 * @return
	 */
	public HAuthRole findById(Integer roleId) {

		StringBuffer sb = new StringBuffer();
		sb.append("select * from ").append(TABLENAME);
		sb.append(" where ").append(ID).append(" = ");
		sb.append(roleId);

		if (log.isDebugEnabled()) {
			log.info("updating " + TABLENAME + " instance with id: " + roleId);
			log.info(sb.toString());
		}

		QueryRunner run = new QueryRunner(dbUtil.dataSource(getDataSourceName()));
		BeanHandler h = new BeanHandler(HAuthRole.class);
		HAuthRole role = null;
		try {
			role = (HAuthRole) run.query(sb.toString(), h);
		} catch (SQLException e) {
			log.error(e.getMessage());
		}
		return role;
	}

	/**
	 * 检索所有角色.
	 * 
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public ArrayList<HAuthRole> findAll() {
		StringBuffer sb = new StringBuffer();
		sb.append("select * from ").append(TABLENAME);
		sb.append(" order by ").append(ID);

		if (log.isDebugEnabled()) {
			log.info("getting " + TABLENAME + " all instance");
			log.info(sb.toString());
		}

		QueryRunner run = new QueryRunner(dbUtil.dataSource(getDataSourceName()));
		BeanListHandler h = new BeanListHandler(HAuthRole.class);
		ArrayList<HAuthRole> roles = null;
		try {
			roles = (ArrayList<HAuthRole>) run.query(sb.toString(), h);
		} catch (SQLException e) {
			log.error(e.getMessage());
		}
		return roles;
	}

	/**
	 * 检索所有由某用户创建的角色,包括自身.
	 * 
	 * @param roleId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public ArrayList<HAuthRole> findAll(Integer userId) {
		StringBuffer sb = new StringBuffer();
		sb.append("select * from ").append(TABLENAME);
		sb.append(" where ");
		sb.append(USERID).append("=").append(userId);

		if (log.isDebugEnabled()) {
			log.info("getting " + TABLENAME + " all instance");
			log.info(sb.toString());
		}

		QueryRunner run = new QueryRunner(dbUtil.dataSource(getDataSourceName()));
		BeanListHandler h = new BeanListHandler(HAuthRole.class);
		ArrayList<HAuthRole> roles = null;
		try {
			roles = (ArrayList<HAuthRole>) run.query(sb.toString(), h);
		} catch (SQLException e) {
			log.error(e.getMessage());
		}
		return roles;
	}

	/**
	 * 检索指定角色名的所有角色.
	 * 
	 * @param rolename
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<HAuthRole> findByRolename(String rolename) {
		List<HAuthRole> list = null;

		StringBuffer sb = new StringBuffer();
		sb.append("select * from ").append(TABLENAME).append(" where ");
		sb.append(NAME).append("=").append("'").append(rolename).append("'");
		
		if (log.isDebugEnabled()) {
			log.info("getting " + TABLENAME + " all instance with rolename");
			log.info(sb.toString());
		}
		
		QueryRunner run = new QueryRunner(dbUtil.dataSource(getDataSourceName()));
		BeanListHandler h = new BeanListHandler(HAuthRole.class);
		try {
			list = (List<HAuthRole>) run.query(sb.toString(), h);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	private static final Log log = LogFactory.getLog(HAuthRoleDAO.class);

	private String dataSourceName;

	public String getDataSourceName() {
		return dataSourceName;
	}

	public void setDataSourceName(String dataSourceName) {
		this.dataSourceName = dataSourceName;
	}

	private static final String TABLENAME = "h_auth_roles";
	private static final String TABLESEQ = "h_auth_role_seq";
	public static final String ID = "id";
	public static final String NAME = "name";
	public static final String NOTE = "note";
	public static final String USERID = "userid";

}